package net.dotool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import net.dotool.db.SubmitProcess;
import net.dotool.impl.AResultSetReader;

/**
 * 数据库相关
 * @author 李岩飞
 * @email eliyanfei@126.com	
 * 2017年10月27日 下午4:19:32
 * 
 */
public class ConnectionTools {
	/**
	 * @param conn
	 * @param sql PreparedStatement
	 * @param count 多少行提交一次
	 * @param closeConn 释放关闭连接资源
	 * @return
	 */
	public static SubmitProcess createSubmit(Connection conn, String sql, int count, boolean closeConn) {
		return new SubmitProcess(conn, sql, count, closeConn);
	}

	public static SubmitProcess createSubmit(Connection conn, String sql, int count) {
		return new SubmitProcess(conn, sql, count, true);
	}

	public static SubmitProcess createSubmit(Connection conn, String sql, boolean closeConn) {
		return new SubmitProcess(conn, sql, 2000, closeConn);
	}

	public static SubmitProcess createSubmit(Connection conn, String sql) {
		return new SubmitProcess(conn, sql, 2000, true);
	}

	/**
	 * 
	 * @param reader 回调函数
	 * @param conn 数据库连接
	 * @param sql SQL语句
	 * @param closeConn 是否关闭数据库连接
	 */
	public static void readResult(AResultSetReader reader, Connection conn, String sql, boolean closeConn) {
		Statement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.createStatement();
			rs = ps.executeQuery(sql);
			while (rs.next()) {
				try {
					reader.readRow(rs);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, ps, closeConn ? conn : null);
		}
	}

	/**
	 * 查询SQL语句，1作为key，2作为value转换为Map对象
	 * @param conn
	 * @param sql SQL语句
	 * @param cla 转换的类型
	 * @param closeConn 是否关闭connection连接
	 * @return
	 */
	public static <T> Map<String, T> sqlToMap(Connection conn, String sql, Class<T> cla, boolean closeConn) {
		Map<String, T> map = new HashMap<String, T>();
		Statement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.createStatement();
			rs = ps.executeQuery(sql);
			while (rs.next()) {
				map.put(rs.getString(1), cla.cast(rs.getObject(2)));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, ps, closeConn ? conn : null);
		}
		return map;
	}

	/**
	 * 
	 * @param jdbcResultSet
	 * @param statement
	 * @param connection @ deprecated 本方法只适合关闭普通的连接，考虑到系统中将所有由Spring创建的数据库连接监制到，
	 *            使用 {@link #closeAllForPool(ResultSet, Statement, Connection)}
	 */
	public static void closeAll(final ResultSet rs, Statement st, final Connection conn) {
		try {
			if (rs != null)
				rs.close();
		} catch (final SQLException e) {
		}
		try {
			if (st != null)
				st.close();
		} catch (final SQLException e) {
			st = null;
		}
		try {
			if (conn != null)
				conn.close();
		} catch (final SQLException e) {
		}
	}

	/**
	 * 组织指定数据库(以conn为数据源)的表tableName的完整的insert语句
	 * 
	 * @param tableName
	 * @param conn
	 * @return
	 */
	public static final String getInsertSql(final String tableName, final Connection conn) {
		final StringBuffer buf = new StringBuffer();
		try {
			final List<String> lables = getTableLabels(tableName, conn);
			buf.append("INSERT INTO ").append(tableName).append(" (");
			final StringBuffer vBuf = new StringBuffer();
			boolean isFirst = true;
			for (final String label : lables) {
				if (isFirst)
					isFirst = false;
				else {
					buf.append(",");
					vBuf.append(",");
				}
				buf.append(label);
				vBuf.append("?");
			}
			buf.append(" ) ").append(" VALUES(").append(vBuf.toString()).append(")");
		} catch (final Exception e) {
		}

		return buf.toString();
	}

	/**
	 * 获取数据库表的列标签
	 * @param tableName
	 * @param conn
	 * @return
	 */
	public static final List<String> getTableLabels(final String tableName, final Connection conn) {
		final List<String> result = new ArrayList<String>();

		Statement stmt = null;
		ResultSet rs = null;
		try {
			final String sql = StringTools.u("SELECT * FROM (", tableName, ") WHERE 1>1");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			final ResultSetMetaData metaData = rs.getMetaData();
			final int colsLen = metaData.getColumnCount() + 1;

			for (int i = 1; i < colsLen; i++) {
				result.add(metaData.getColumnLabel(i).toUpperCase());
			}
		} catch (final Exception e) {
			//e.printStackTrace();
		} finally {
			closeAll(rs, stmt, null);
		}
		return result;
	}

	public static void query(Connection conn, String sql, Object[] params, IRow row) {
		query(conn, sql, params, row, true);
	}

	public static void query(Connection conn, String sql, Object[] params, IRow row, boolean closeConn) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			if (params != null) {
				int i = 1;
				for (Object obj : params) {
					ps.setObject(i++, obj);
				}
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				try {
					row.row(rs);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, ps, closeConn ? conn : null);
		}
	}

	public static Map<String, Object> queryMap(Connection conn, String sql, Object... params) {
		final Map<String, Object> map = new HashMap<String, Object>();
		query(conn, sql, params, new IRow() {
			public void row(ResultSet rs) throws Exception {
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
					map.put(rs.getMetaData().getColumnName(i + 1), rs.getObject(i + 1));
				}
			}
		});
		return map;
	}

	public static List<Map<String, Object>> queryList(Connection conn, String sql, Object... params) {
		final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		query(conn, sql, params, new IRow() {
			public void row(ResultSet rs) throws Exception {
				final Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
					map.put(rs.getMetaData().getColumnName(i + 1), rs.getObject(i + 1));
				}
				list.add(map);
			}
		});
		return list;
	}

	public static <T> Set<T> queryOneSet(Connection conn, String sql, Object[] params, final Class<T> t) {
		final Set<T> set = new HashSet<T>();
		query(conn, sql, params, new IRow() {
			public void row(ResultSet rs) throws Exception {
				set.add(t.cast(rs.getObject(1)));
			}
		});
		return set;
	}

	public static <T> List<T> queryOneList(Connection conn, String sql, Object[] params, final Class<T> t) {
		final List<T> set = new ArrayList<T>();
		query(conn, sql, params, new IRow() {
			public void row(ResultSet rs) throws Exception {
				set.add(t.cast(rs.getObject(1)));
			}
		});
		return set;
	}

	public static <T> Map<String, T> queryOneMap(Connection conn, String sql, Object[] params, final Class<T> t, final String key, final String value) {
		final Map<String, T> map = new LinkedHashMap<String, T>();
		query(conn, sql, params, new IRow() {
			public void row(ResultSet rs) throws Exception {
				map.put(rs.getString(key), t.cast(rs.getObject(value)));
			}
		});
		return map;
	}
}
